<?php
class Event {
	
	/**
     * @var object Zend DB Adapter
     */
    private $_db;
    /**
     * @var string
     */
   
    public function __construct()
    {
		$this->_db = Zend_Registry::get('db');
    }
    
    public function getEventsByUserId($userid)
    {
    	$event_arr = $this->_db->fetchAll("
    		SELECT E.*, EU.activity AS ACTIVITY_SUB 
    		FROM events E
    		LEFT JOIN event_sub_user EU ON EU.event_id = E.event_id
    		WHERE E.user_id = " . $userid . "
    		GROUP BY E.event_id");
    	
    	return $event_arr;
    }
    
    public function getEventDetailsById($eventid, $userid)
    {
    	/*$event_arr = $this->_db->fetchRow("
    		SELECT * 
    		FROM events
    		WHERE event_id = " . $eventid
    	);*/
    	$event_arr = $this->_db->fetchRow("
    		SELECT E.*, EU.activity AS ACTIVITY_SUB 
    		FROM EVENTS E
    		LEFT JOIN event_sub_user EU ON EU.event_id = E.event_id
    		WHERE E.event_id = " . $eventid . " AND E.user_id = " . $userid
    	);
    	
    	return $event_arr;
    }
    
    public function getEventsByEventId($eventid)
    {
    	$event_arr = $this->_db->fetchAll("
    		SELECT EU.activity AS ACTIVITY_SUB, U.first_name, U.last_name, U.userlevel 
    		FROM EVENTS E
    		LEFT JOIN event_sub_user EU ON EU.event_id = E.event_id
    		LEFT JOIN user U ON U.userid = EU.user_id
    		WHERE EU.event_id = " . $eventid);
    	
    	return $event_arr;
    }
    
    private function getSerialNo($eventid)
    {
    	$eventid = $eventid - 1;
    	$serial_no = $this->_db->fetchRow("
    		SELECT serial_no
    		FROM events
    		WHERE event_id = " . $eventid
    	);
    	
    	return $serial_no;
    }
	
	public function addEvent($form_values, $userid)
	{
		$customer = $form_values['customer'];
    	$event_date_format = $form_values['event_date'];
    	$event_date = explode("/", $event_date_format);
    	$event_date = $event_date[2] . "-" . $event_date[1] . "-" . $event_date[0];    	
    	$time_chargeble = $form_values['time_charge'];
    	$transport = $form_values['transport'];
    	$service_agreement = $form_values['service_agreement'];
    	$activity = addslashes($form_values['activity']);
    	$additional_text = addslashes($form_values['additional_text']);
    	$start_time = $form_values['start_time'];
    	$end_time = $form_values['end_time'];
    	$event_type = $form_values['event_type'];
    	
    	$int_serial_no = 1000000; 
    	$add_event = $this->_db->exec("
    		INSERT INTO events(user_id, customer_id, date, time_charge, transport, service_agreement, start_time, end_time, additional_text, event_type)
    		VALUES(" . $userid . ", " . $customer . ", '" . $event_date . "', " . $time_chargeble . ", " . $transport . ", " . $service_agreement . ", '" . $start_time . "', '" . $end_time . "', '" . $additional_text . "', '" . $event_type . "')"); 

    	/*if($add_event)
    	{
    		$last_eventid = $this->_db->lastInsertId('event_id'); 
    		
    		$sql = $this->_db->exec("
    			INSERT INTO event_sub_user(event_id, user_id, activity)
    			VALUES(" . $last_eventid . ", " . $userid . ", '" . $activity . "')
    		");
    		
    		$getSerialNo = $this->getSerialNo($last_eventid);
    		$db_serial_no = $getSerialNo['serial_no'];
    		
    		if($db_serial_no == "")
    		{
    			$serial_no = $int_serial_no;
    		} else {
    			$serial_no = $db_serial_no + 1;
    		}
    		
    		$add_serial_no = $this->_db->exec("
    			UPDATE events 
    			SET serial_no = " . $serial_no . "
    			WHERE event_id = " . $last_eventid);
    		
    		return true;
    	}*/
	}
	
	public function getUsersForEvents()
	{
		$event_user_arr = $this->_db->fetchAll(
			"SELECT userid, first_name, last_name, userlevel
			 FROM user
			 WHERE userlevel != 'superadmin' 
			 AND userlevel != 'admin'");
			
		return $event_user_arr;
	}
	
	public function getEventUserDetailsByUserId($userid)
	{
		foreach ($userid as $user_entry) {
			$user_event_arr = $this->_db->fetchRow("
			SELECT U.first_name, U.last_name
			FROM user U
			LEFT JOIN events E ON E.user_id = U.userid
			WHERE U.userid = " . $user_entry);
		
			return $user_event_arr;
		}
	}
	
	public function updateEvent($form_values, $userid)
	{
		$event_id = $form_values['event_id'];
		$customer = $form_values['customer'];
    	$event_date = $form_values['event_date'];
    	$time_chargeble = $form_values['time_charge'];
    	$transport = $form_values['transport'];
    	$service_agreement = $form_values['service_agreement'];
    	$activity = ltrim($form_values['activity']);
    	$activity = str_replace("<p>", "", $activity);
    	$activity = str_replace("</p>", "", $activity);
    	$addional_text = $form_values['additional_text'];
    	$start_time = $form_values['start_time'];
    	$end_time = $form_values['end_time'];
    	$event_type = $form_values['event_type'];
    	
    	$update_event = $this->_db->exec("
    		UPDATE events 
    		SET customer_id = " . $customer . ", date = '" . $event_date . "', 
    			time_charge = " . $time_chargeble . ", transport = " . $transport . ", service_agreement = " . $service_agreement . ", 
    			start_time = '" . $start_time . "', end_time = '" . $end_time . "', additional_text = '" . $addional_text . "', event_type = '" . $event_type . "'
    		WHERE event_id = " . $event_id);

    	if($update_event)
    	{
    		echo "Event details has been successfully updated";    		
    	}
	}
	
	public function getCustomerNoteByEventId($eventid)
	{
		$arr = $this->_db->fetchRow("
			SELECT cust_note 
			FROM events
			WHERE event_id = " . $eventid
		);
		
		return $arr;
	}
	
	public function updateCustNote($form_values, $userid, $eventid)
	{
		$cust_note = $form_values['custnote'];
		$addcustnote = $this->_db->exec("
			UPDATE events 
			SET cust_note = '" . $cust_note . "' 
			WHERE event_id = " . $eventid . " 
			AND user_id = " . $userid
		);
		
		if($addcustnote) {
			echo "Customer note has been successfully updated";
		}
	}
	
	public function getTimeDifference( $start, $end )
	{
	    $uts['start'] = strtotime( $start );
	    $uts['end']   = strtotime( $end );
	    if( $uts['start'] !== -1 && $uts['end'] !== -1 )
	    {
	        if( $uts['end'] >= $uts['start'] )
	        {
	            $diff = $uts['end'] - $uts['start'];
	            if( $days=intval((floor($diff/86400))) )
	                $diff = $diff % 86400;
	            if( $hours=intval((floor($diff/3600))) )
	                $diff = $diff % 3600;
	            if( $minutes=intval((floor($diff/60))) )
	                $diff = $diff % 60;
	            $diff    =    intval( $diff );            
	            return( array('days'=>$days, 'hours'=>$hours, 'minutes'=>$minutes, 'seconds'=>$diff) );
	        }
	        else
	        {
	            trigger_error( "Ending date/time is earlier than the start date/time", E_USER_WARNING );
	        }
	    }
	    else
	    {
	        trigger_error( "Invalid date/time data detected", E_USER_WARNING );
	    }
	    return( false );
	}	
	
	public function getMaxEventId() 
	{
		$max_event_id = $this->_db->fetchRow("
			SELECT MAX(event_id) as MAXEVENTID
			FROM events");
		return $max_event_id;
	}
	
	public function addTempSubUser($userid, $max_event_id, $sub_activity)
	{
		$new_event_id = $max_event_id + 1;	
		
		$sql = "SELECT user_id
			FROM event_sub_user
			WHERE user_id = " . $userid . "
			AND event_id = " . $new_event_id;
		
		$sub_usr_arr = $this->_db->fetchAll($sql);
		
		//print_r($sub_usr_arr); exit;
		
		if(count($sub_usr_arr) > 0)
		{
			$this->_db->exec("
				UPDATE event_sub_user
				SET activity = '" . $sub_activity . "' 
				WHERE user_id = " . $userid . " 
				AND event_id = " . $new_event_id);					
		} else {
			$this->_db->exec("
				INSERT INTO event_sub_user(event_id, user_id, activity)
				VALUES(" . $new_event_id . ", " . $userid . ", '" . $sub_activity . "')");
		}
		
		//return true;
	}
	
	public function editTempSubUser($userid, $event_id, $sub_activity, $eventid)
	{
		$sql = "SELECT user_id
			FROM event_sub_user
			WHERE user_id = " . $userid . "
			AND event_id = " . $eventid;
		
		$sub_usr_arr = $this->_db->fetchAll($sql);
		//print_r($sub_usr_arr); exit;
		if(count($sub_usr_arr) > 0)
		{
			$this->_db->exec("
				UPDATE event_sub_user
				SET activity = '" . addslashes($sub_activity) . "' 
				WHERE user_id = " . $userid . " 
				AND event_id = " . $eventid);					
		} else {
			$this->_db->exec("
				INSERT INTO event_sub_user(event_id, user_id, activity)
				VALUES(" . $event_id . ", " . $userid . ", '" . addslashes($sub_activity) . "')");
		}
	}
	
	public function getSubUserActivityByEvent($eventid)
	{
		$sql = "SELECT user_id, activity
			FROM event_sub_user
			WHERE event_id = " . $eventid;
		
		$sub_user_arr = $this->_db->fetchAll($sql);
		
		return $sub_user_arr;
	}
	
	public function deleteTmpSubUser($userid, $eventid)
	{
		$del_tmp_sub_usr = $this->_db->exec("
			DELETE FROM event_sub_user
			WHERE user_id = " . $userid . "
			AND event_id = " . $eventid);		
	}
}
?>